package com.shms.dao.impl;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.shms.bean.GroupOfPoint;
import com.shms.bean.IndexParam;
import com.shms.dao.ShPointDao;
import com.shms.entity.ShBuild;
import com.shms.entity.ShGrade;
import com.shms.entity.ShHome;
import com.shms.entity.ShPoint;
import com.shms.until.PageUntil;

/**
 * 文件名：ShPointDaoImpl.java
 * 编写者：习孟冬
 * 创建时间：2013-1-3
 * 简要描述：
 *
 */
public class ShPointDaoImpl extends HibernateDaoSupport implements ShPointDao {
	@SuppressWarnings("finally")
	public List<ShPoint> queryPointByGradeAndWeek(ShPoint shPoint) {
		List<ShPoint> shPoints = new ArrayList<ShPoint>();
		try {
			/*
			 * 构造本地查询SQL语句
			 * 参数的绑定：将命名参数与一个对象的属性值绑定在一起，
			 * 即参数的用(:参数名)形式绑定，且此参数名是传入的对象的一个属性字段
			 * 用setProperties()的方法绑定实体对象
			 */
			StringBuffer strBuf = new StringBuffer();
			strBuf.append("SELECT P.*, H.*, B.*");
			strBuf.append(" FROM SH_POINT P, SH_HOME H, SH_BUILD B");
			strBuf.append(" WHERE P.WEEK = :week AND P.HID IN");
			strBuf.append(" (SELECT S.HID FROM SH_STUDENT S");
			strBuf.append(" WHERE S.CID IN");
			strBuf.append(" (SELECT C.CID FROM SH_CLASS C WHERE C.GID = :pid)");
			strBuf.append(" GROUP BY S.HID) AND P.HID = H.HID AND P.BID = B.BID");
						
			SQLQuery sqlQuery = this.getSessionFactory().getCurrentSession().createSQLQuery(strBuf.toString());
			//为SQL语句绑定参数
			sqlQuery.setProperties(shPoint);
			/*
			 * addEntity();
			 * 将查询的结果封装到这三个实体类中，注意：
			 * 1、查询返回的是某个数据表的全部数据列
			 * 2、该数据表有对应的持久化类映射
			 */
			@SuppressWarnings("rawtypes")
			List list = sqlQuery.addEntity(ShPoint.class).addEntity(ShHome.class).addEntity(ShBuild.class).list();
			for(@SuppressWarnings("rawtypes")
			Iterator iterator = list.iterator(); iterator.hasNext();){  
		        //每个集合元素都是ShPoint、ShHome、ShBuild所组成的数组  
		        Object[] objects = (Object[]) iterator.next();  
		        ShPoint shPointRtn = (ShPoint) objects[0];  
		        ShHome shHomeRtn = (ShHome) objects[1]; 
		        ShBuild shBuildRtn = (ShBuild) objects[2];
		        //拼接字段，楼名+宿舍号，借用字段Park存放
		        shPointRtn.setPark1(shBuildRtn.getBname() + shHomeRtn.getHno());
		        //构造返回的ShPoint类的查询结果
		        shPoints.add(shPointRtn);
		    } 
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			return shPoints;
		}
	}
	
	@SuppressWarnings({ "finally", "unchecked" })
	public List<ShPoint> queryPointByBidAndHid(ShPoint shPoint) {
		List<ShPoint> shPoints = new ArrayList<ShPoint>();
		try {
			StringBuffer strBuf = new StringBuffer();
			strBuf.append("SELECT P.* FROM SH_POINT P");
			strBuf.append(" WHERE P.HID = :hid AND P.BID = :bid");  
			strBuf.append(" ORDER BY P.WEEK");
		       
			SQLQuery sqlQuery = this.getSessionFactory().getCurrentSession().createSQLQuery(strBuf.toString());
			//为SQL语句绑定参数
			sqlQuery.setProperties(shPoint); 
			//将查询的结果封装到实体类中
			shPoints = sqlQuery.addEntity(ShPoint.class).list();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			return shPoints;
		}
	}
	
	@SuppressWarnings({ "unchecked", "finally" })
	public List<ShPoint> avgPointByHid(ShGrade shGrade) {
		List<ShPoint> shPoints = new ArrayList<ShPoint>();
		try {
			StringBuffer strBuf = new StringBuffer();
			strBuf.append("SELECT B.BNAME AS park1,");
			strBuf.append("  H.HNO AS park2,");
			strBuf.append(" ROUND(AVG(P.PGRADE), 2) AS pgrade");
			strBuf.append(" FROM SH_POINT P, SH_HOME H, SH_BUILD B");
			strBuf.append(" WHERE P.HID IN");
			strBuf.append(" (SELECT S.HID");
			strBuf.append(" FROM SH_STUDENT S");
			strBuf.append(" WHERE S.CID IN (SELECT C.CID FROM SH_CLASS C WHERE C.GID = :gid))");
			strBuf.append(" AND P.HID = H.HID");
			strBuf.append(" AND H.BID = B.BID");
			strBuf.append(" GROUP BY P.HID, H.HNO, B.BNAME");
			strBuf.append(" ORDER BY B.BNAME");
			
			SQLQuery sqlQuery = this.getSessionFactory().getCurrentSession().createSQLQuery(strBuf.toString());
			/*
			 * 将查询结果转换成标量值
			 * 将查询结果封装到没有映射的实体bean中，
			 * 绑定要查询的字段，并指明类型，
			 * 绑定的字段要和要封装到的实体中的字段对应，大小要一致，类型要一致，
			 * 还要与查询的sql语句的中查询字段的别名一致
			 */
			sqlQuery.addScalar("park1", Hibernate.STRING).addScalar("park2", Hibernate.STRING).addScalar("pgrade", Hibernate.DOUBLE);
			//查询结果转换为T对象
			sqlQuery.setResultTransformer(Transformers.aliasToBean(ShPoint.class));
			//为SQL语句绑定参数
			sqlQuery.setProperties(shGrade);
			//查询
			shPoints = sqlQuery.list();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			return shPoints;
		}
	}
	
	@SuppressWarnings("finally")
	public GroupOfPoint queryPoint(GroupOfPoint groupOfPoint) {
		GroupOfPoint gOfPointRtn = new GroupOfPoint();
		try {
			String sql = "SELECT COUNT(*) FROM SH_POINT P";
			
			SQLQuery sqlQuery = this.getSessionFactory().getCurrentSession().createSQLQuery(sql);
			//获取总条数
			int allRow = Integer.parseInt(String.valueOf(sqlQuery.uniqueResult()));
			//获取总页数
			int totalPage = PageUntil.countTotalPage(groupOfPoint.getPageSize(), allRow);
			//获取要查询显示的数据的页数
			int currentPage = PageUntil.countCurrentPage(groupOfPoint.getCurrentPage(), totalPage);
			//获取要查询显示的数据的第一条的数字
			int currentRow = PageUntil.countCurrentRow(groupOfPoint.getPageSize(), currentPage);
			
			StringBuffer strSql = new StringBuffer();
			strSql.append("SELECT P.PID AS pid, P.PGRADE AS pgrade, H.HNO AS park1, B.BNAME AS park2, P.ADDTIME AS addtime, P.WEEK AS week");
			strSql.append(" FROM SH_POINT P, SH_HOME H, SH_BUILD B");
			strSql.append(" WHERE P.HID = H.HID AND P.BID = B.BID");
			strSql.append(" ORDER BY P.WEEK");
			sqlQuery = this.getSessionFactory().getCurrentSession().createSQLQuery(strSql.toString());
			sqlQuery.addScalar("pid", Hibernate.LONG).addScalar("pgrade", Hibernate.DOUBLE).addScalar("park1", Hibernate.STRING).addScalar("park2", Hibernate.STRING).addScalar("addtime", Hibernate.TIMESTAMP).addScalar("week", Hibernate.STRING);
			//查询结果转换为T对象
			sqlQuery.setResultTransformer(Transformers.aliasToBean(ShPoint.class));
			//设置查询的第一条数据位置
			sqlQuery.setFirstResult(currentRow);
			//设置查询最大条数
			sqlQuery.setMaxResults(groupOfPoint.getPageSize());
			@SuppressWarnings("unchecked")
			List<ShPoint> pointList = sqlQuery.list();
			gOfPointRtn = new GroupOfPoint(currentPage, totalPage, allRow, pointList); 
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			return gOfPointRtn;
		}
	}
	
	@SuppressWarnings("finally")
	public boolean batchDelete(Integer[] ids) {
		boolean rtn = true;
		try {
			String sql = "DELETE FROM SH_POINT P WHERE P.PID IN (:ids)";
			SQLQuery sqlQuery = this.getSessionFactory().getCurrentSession().createSQLQuery(sql);
			sqlQuery.setParameterList("ids", ids);
			sqlQuery.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
			rtn = false;
		} finally {
			return rtn;
		}
	}
	
	@SuppressWarnings("finally")
	public boolean savePoint(ShPoint shPoint) {
		boolean rtn = true;
		try {
			this.getSessionFactory().getCurrentSession().save(shPoint);
		} catch (Exception ex) {
			ex.printStackTrace();
			rtn = false;
		} finally {
			return rtn;
		}
	}
	
	@SuppressWarnings("finally")
	public boolean updatePoint(ShPoint shPoint) {
		boolean rtn = true;
		try {
			this.getSessionFactory().getCurrentSession().update(shPoint);
		} catch (Exception ex) {
			ex.printStackTrace();
			rtn = false;
		} finally {
			return rtn;
		}
	}
	
	@SuppressWarnings("finally")
	public ShPoint queryById(ShPoint shPoint) {
		ShPoint shPointRtn = new ShPoint();
		try {
			String sql = "SELECT * FROM SH_POINT WHERE PID = :pid";
			SQLQuery sqlQuery = this.getSessionFactory().getCurrentSession().createSQLQuery(sql);
			sqlQuery.setProperties(shPoint);
			@SuppressWarnings("unchecked")
			List<ShPoint> listPoint = sqlQuery.addEntity(ShPoint.class).list();
			if (listPoint.size() == 1) {
				shPointRtn = listPoint.get(0);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			return shPointRtn;
		}
	}
	
	@SuppressWarnings({ "finally", "unchecked" })
	@Override
	public List<ShPoint> queryPointByExample(IndexParam indexParam) {
		List<ShPoint> list = new ArrayList<ShPoint>();
		try {
			StringBuffer strBuf = new StringBuffer();
			strBuf.append(" SELECT * FROM ");
			strBuf.append(" (SELECT P.PID AS pid, P.PGRADE AS pgrade, P.ADDTIME AS addtime, P.WEEK AS week, P.PARK3 AS park3, H.HID AS hid, H.HNO AS park1, B.BID AS bid, B.BNAME AS park2 ");
			strBuf.append(" FROM SH_POINT P, SH_HOME H, SH_BUILD B WHERE ");
			strBuf.append(" (to_char(P.ADDTIME,'yyyymmdd') >= (select to_char(SYSDATE,'yyyymmdd')-to_number(to_char(SYSDATE,'d')-1)-6 from dual)) AND ");
			strBuf.append(" (to_char(P.ADDTIME,'yyyymmdd') <= (select to_char(SYSDATE,'yyyymmdd')-to_number(to_char(SYSDATE,'d')-1) from dual)) AND ");
			strBuf.append(" P.HID = H.HID AND P.BID = B.BID ");
			strBuf.append(" ORDER BY P.PGRADE DESC) WHERE ");
			strBuf.append(" ROWNUM <=4 ");
			SQLQuery sqlQuery = this.getSessionFactory().getCurrentSession().createSQLQuery(strBuf.toString());
			sqlQuery.setProperties(indexParam);
			list = sqlQuery.addEntity(ShPoint.class).list();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			return list;
		}
	}
}
